


ALTER              proc sp_create_update_assembly_release_details
 @assembly_release_id int,
 @ps_type_name varchar(20),
 @lot_id bigint,
 @wafer_qty int,
 @die_qty int,
 @wafer_id varchar(50),
 @die_qty_pull int,
 @wafer_qty_pull int,
 @lot_or_wafer_selected int
 
 
 as
 begin
 
 declare @temp_assembly_release_id int
 declare @old_lot_or_wafer_status int

 	if ( @ps_type_name = 'SORT' or @ps_type_name = 'Die Bank' )
 	begin
 		if @wafer_id <> ''
		begin
	 		select @temp_assembly_release_id = AssemblyRelease_id, @old_lot_or_wafer_status = wafer_selected from assembly_release_bin_information where assemblyrelease_id = @assembly_release_id and lot_id = @lot_id and Wafer_ID = @wafer_id
	 		
	 		if ( @@rowcount = 0 )
	 		begin
	 			insert into assembly_release_bin_information ( assemblyrelease_id, lot_id, wafer_id, Qty_pull, Wafer_Selected ) values ( @assembly_release_id, @lot_id, @wafer_id, @wafer_qty_pull, @lot_or_wafer_selected )
	 		end
	 		else if ( @old_lot_or_wafer_status = 1 and @lot_or_wafer_selected = 0 )
	 		begin
	 			delete from assembly_release_bin_information where assemblyrelease_id = @assembly_release_id and lot_id = @lot_id and Wafer_ID = @wafer_id
	 		end
	 		else 
	 		begin
	 			update assembly_release_bin_information set wafer_id = @wafer_id, Qty_pull = @wafer_qty_pull where assemblyrelease_id = @assembly_release_id and lot_id = @lot_id and Wafer_ID = @wafer_id
	 		end
		end
 
 	end
 
 	if ( @ps_type_name = 'FAB' or @ps_type_name = 'Wafer Store' or @ps_type_name = 'Die Bank' or @ps_type_name = 'SORT' )
 	begin
 		if @wafer_id = ''
		begin
			select @temp_assembly_release_id = AssemblyRelease_id, @old_lot_or_wafer_status = lot_selected from assembly_release_lot_table where assemblyrelease_id = @assembly_release_id and lot_id = @lot_id 
	 
	 		if ( @@rowcount = 0 )
	 		begin
	 			insert into assembly_release_lot_table ( Assemblyrelease_Id, Lot_Id, Wafer_Qty, Die_Qty, Lot_Selected ) values ( @assembly_release_id, @lot_id, @wafer_qty_pull, @die_qty_pull, @lot_or_wafer_selected  )
	 		end
	 		else if ( @old_lot_or_wafer_status = 1 and @lot_or_wafer_selected = 0 )
	 		begin
	 			delete from assembly_release_lot_table where assemblyrelease_id = @assembly_release_id and lot_id = @lot_id 
	 		end
	 		else
	 		begin
	 			update assembly_release_lot_table set Die_Qty = @die_qty_pull, Wafer_Qty = @wafer_qty_pull where assemblyrelease_id = @assembly_release_id and lot_id = @lot_id 
	 		end
		end
 	end
 end
